--- layout: documentation title: Malloy Documentation footer: /generated/footers/examples/faa.html ---

NTSB Flight Database examples

The follow examples all run against the model at the bottom of this page OR you can find the source code here.

Airport Dashboard

Where can you fly from SJC? For each destination; Which carriers? How long have they been flying there? Are they ontime?

query: flights -> airport_dashboard {where: origin.code: 'SJC'}
code destination flight_​count carriers_​by_​month routes_​map delay_​by_​hour_​of_​day
LAX LOS ANGELES INTL 58,815 200020022004dep_month0200400flight_countAmericanAmerican EagleSky WestSouthwestUnitednickname 58,815flight_count 05101520dep_hour02040flight_count31489delay
SAN SAN DIEGO INTL-LINDBERGH FLD 34,702 200020022004dep_month0100200300flight_countAmericanAmerican EagleContinentalSouthwestnickname 34,702flight_count 05101520dep_hour0102030flight_count31730delay
SNA JOHN WAYNE AIRPORT-ORANGE COUNTY 30,907 200020022004dep_month0100200flight_countAmericanAmerican EagleSouthwestnickname 30,907flight_count 05101520dep_hour01020flight_count200400600delay
LAS MC CARRAN INTL 25,825 200020022004dep_month0100200flight_countAmerica WestAmericanSouthwestnickname 25,825flight_count 05101520dep_hour01020flight_count31739delay
SEA SEATTLE-TACOMA INTL 24,864 200020022004dep_month0100200300flight_countAlaskaAmericanHawaiianSouthwestnickname 24,864flight_count 05101520dep_hour01020flight_count200400600delay
PHX PHOENIX SKY HARBOR INTL 24,310 200020022004dep_month0100200flight_countAmerica WestAmericanAmerican EagleSouthwestnickname 24,310flight_count 05101520dep_hour0510flight_count31577delay
ORD CHICAGO O'HARE INTL 21,997 200020022004dep_month0100200flight_countAmericanUnitednickname 21,997flight_count 05101520dep_hour051015flight_count31534delay
PDX PORTLAND INTL 21,562 200020022004dep_month0100200flight_countAlaskaAmericanSouthwestnickname 21,562flight_count 05101520dep_hour01020flight_count31769delay
BUR BURBANK-GLENDALE-PASADENA 17,557 200020022004dep_month0100200flight_countSouthwestnickname 17,557flight_count 05101520dep_hour010203040flight_count100200300delay
ONT ONTARIO INTL 17,358 200020022004dep_month0100200300flight_countSky WestSouthwestnickname 17,358flight_count 05101520dep_hour01020flight_count31335delay

Carrier Dashboard

Tell me everything about a carrier. How many destinations?, flights? hubs? What kind of planes to they use? How many flights over time? What are the major hubs? For each destionation, How many flights? Where can you? Have they been flying there long? Increasing or decresing year by year? Any seasonality?

query: flights -> carrier_dashboard {where: carriers.nickname : 'Jetblue'}
destination_​count flight_​count by_​manufacturer by_​month hubs origin_​dashboard
33 267,963 0204060aircraft_countAIRBUSAIRBUS INDUSTRIEEMBRAERnull1,151161,208flight_count 200320042005dep_month05,00010,000flight_count
hub destination_​count
JFK - JOHN F KENNEDY INTL 29
BOS - GENERAL EDWARD LAWRENCE LOGAN INTL 14
LGB - LONG BEACH /DAUGHERTY FIELD/ 9
FLL - FORT LAUDERDALE/HOLLYWOOD INTL 7
OAK - METROPOLITAN OAKLAND INTL 7
IAD - WASHINGTON DULLES INTERNATIONAL 6
EWR - NEWARK INTL 6
PBI - PALM BEACH INTL 4
ONT - ONTARIO INTL 4
LAS - MC CARRAN INTL 3
code
JFK
origin
JOHN F KENNEDY INTL
city
NEW YORK
flight_count
96,992
destinations_by_month
200320042005dep_month0200400flight_countnullBOS - GENERAL EDWA…BQN - RAFAEL HERNA…BTV - BURLINGTON I…BUF - BUFFALO NIAG…BUR - BURBANK-GLEN…DEN - DENVER INTLEWR - NEWARK INTLFLL - FORT LAUDERD…LAS - MC CARRAN IN…LAX - LOS ANGELES …LGB - LONG BEACH /…MCO - ORLANDO INTLMSY - NEW ORLEANS …OAK - METROPOLITAN…ONT - ONTARIO INTLPBI - PALM BEACH I…PDX - PORTLAND INTLPHX - PHOENIX SKY …PSE - MERCEDITAROC - GREATER ROCH…RSW - SOUTHWEST FL…SAN - SAN DIEGO IN…SEA - SEATTLE-TACO…SJC - SAN JOSE INT…SJU - LUIS MUNOZ M…SLC - SALT LAKE CI…SMF - SACRAMENTO I…SYR - SYRACUSE HAN…TPA - TAMPA INTLname
routes_map
113,281flight_count
year_over_year
24681012dep_month01,0002,0003,0004,000flight_countDec 31, 2002Dec 31, 2004dep_year
code
LGB
origin
LONG BEACH /DAUGHERTY FIELD/
city
LONG BEACH
flight_count
23,799
destinations_by_month
200320042005dep_month0100200flight_countATL - THE WILLIAM …BOS - GENERAL EDWA…FLL - FORT LAUDERD…IAD - WASHINGTON D…JFK - JOHN F KENNE…LAS - MC CARRAN IN…OAK - METROPOLITAN…ONT - ONTARIO INTLSLC - SALT LAKE CI…name
routes_map
2,0004,0006,000flight_count
year_over_year
24681012dep_month0200400600800flight_countDec 31, 2002Dec 31, 2004dep_year
code
FLL
origin
FORT LAUDERDALE/HOLLYWOOD INTL
city
FORT LAUDERDALE
flight_count
22,641
destinations_by_month
200320042005dep_month0200400flight_countBOS - GENERAL EDWA…BTV - BURLINGTON I…EWR - NEWARK INTLIAD - WASHINGTON D…JFK - JOHN F KENNE…LGA - LA GUARDIALGB - LONG BEACH /…name
routes_map
113,286flight_count
year_over_year
24681012dep_month05001,000flight_countDec 31, 2002Dec 31, 2004dep_year
code
OAK
origin
METROPOLITAN OAKLAND INTL
city
OAKLAND
flight_count
16,528
destinations_by_month
200320042005dep_month0100200flight_countATL - THE WILLIAM …BOS - GENERAL EDWA…IAD - WASHINGTON D…JFK - JOHN F KENNE…LAX - LOS ANGELES …LGB - LONG BEACH /…ONT - ONTARIO INTLname
routes_map
2,0004,0006,000flight_count
year_over_year
24681012dep_month0200400flight_countDec 31, 2002Dec 31, 2004dep_year
code
MCO
origin
ORLANDO INTL
city
ORLANDO
flight_count
13,006
destinations_by_month
200320042005dep_month0100200300400flight_countBOS - GENERAL EDWA…EWR - NEWARK INTLJFK - JOHN F KENNE…name
routes_map
2,0004,0006,0008,000flight_count
year_over_year
24681012dep_month0200400600flight_countDec 31, 2002Dec 31, 2004dep_year
code
BOS
origin
GENERAL EDWARD LAWRENCE LOGAN INTL
city
BOSTON
flight_count
11,889
destinations_by_month
2004AprilJulyOctober2005AprilJulyOctoberdep_month0100200flight_countDEN - DENVER INTLFLL - FORT LAUDERD…JFK - JOHN F KENNE…LAS - MC CARRAN IN…LAX - LOS ANGELES …LGB - LONG BEACH /…MCO - ORLANDO INTLOAK - METROPOLITAN…ONT - ONTARIO INTLPBI - PALM BEACH I…RSW - SOUTHWEST FL…SEA - SEATTLE-TACO…SJC - SAN JOSE INT…TPA - TAMPA INTLname
routes_map
5001,0001,5002,0002,500flight_count
year_over_year
24681012dep_month05001,000flight_countDec 31, 2003Dec 31, 2004dep_year
code
IAD
origin
WASHINGTON DULLES INTERNATIONAL
city
WASHINGTON
flight_count
9,642
destinations_by_month
200320042005dep_month050100flight_countFLL - FORT LAUDERD…JFK - JOHN F KENNE…LGB - LONG BEACH /…OAK - METROPOLITAN…SAN - SAN DIEGO IN…SMF - SACRAMENTO I…name
routes_map
1,0002,0003,000flight_count
year_over_year
24681012dep_month0100200300flight_countDec 31, 2002Dec 31, 2004dep_year
code
TPA
origin
TAMPA INTL
city
TAMPA
flight_count
8,143
destinations_by_month
200320042005dep_month0100200flight_countBOS - GENERAL EDWA…EWR - NEWARK INTLJFK - JOHN F KENNE…name
routes_map
2,0004,0006,000flight_count
year_over_year
24681012dep_month0100200300400flight_countDec 31, 2002Dec 31, 2004dep_year
code
PBI
origin
PALM BEACH INTL
city
WEST PALM BEACH
flight_count
8,034
destinations_by_month
200320042005dep_month0100200300400flight_countBOS - GENERAL EDWA…EWR - NEWARK INTLJFK - JOHN F KENNE…LGA - LA GUARDIAname
routes_map
2,0004,0006,000flight_count
year_over_year
24681012dep_month0200400flight_countDec 31, 2002Dec 31, 2004dep_year
code
LAS
origin
MC CARRAN INTL
city
LAS VEGAS
flight_count
7,557
destinations_by_month
200320042005dep_month050100150200flight_countBOS - GENERAL EDWA…JFK - JOHN F KENNE…LGB - LONG BEACH /…name
routes_map
1,0002,0003,0004,000flight_count
year_over_year
24681012dep_month0100200300flight_countDec 31, 2002Dec 31, 2004dep_year

Kayak Example Query

Suppose you wanted to build a website like Kayak. Let's assume that the data we have is in the future instead ofthe past. The query below will fetch all the data needed to render a Kayak page in a singe query.

query: flights -> kayak {
  where: [
    origin.code : 'SJC',
    destination.code : 'LAX'|'BUR',
    dep_time : @2004-01-01
  ]
}
carriers by_​hour flights
nickname flight_​count
Southwest 16
American Eagle 11
Sky West 6
dep_​hour flight_​count
6 2
8 2
9 3
10 3
11 1
13 3
14 2
15 3
16 2
17 5
18 2
19 4
21 1
dep_​minute name flight_​num origin_​code destination_​code manufacturer model
2004-01-01 21:05 Southwest Airlines 2866 SJC LAX BOEING 737-317
2004-01-01 19:55 Sky West Airlines 6991 SJC LAX BOMBARDIER INC CL-600-2B19
2004-01-01 19:54 Southwest Airlines 1437 SJC LAX BOEING 737-7H4
2004-01-01 19:40 Southwest Airlines 3353 SJC BUR BOEING 737-7H4
2004-01-01 19:20 American Eagle Airlines 3216 SJC LAX EMBRAER EMB-135KL
2004-01-01 18:20 Southwest Airlines 2484 SJC LAX BOEING 737-3H4
2004-01-01 18:17 Southwest Airlines 565 SJC BUR BOEING 737-317
2004-01-01 17:51 American Eagle Airlines 3152 SJC LAX EMBRAER EMB-135KL
2004-01-01 17:36 Sky West Airlines 6987 SJC LAX BOMBARDIER INC CL-600-2B19
2004-01-01 17:30 Southwest Airlines 495 SJC LAX BOEING 737-3H4
2004-01-01 17:14 American Eagle Airlines 3206 SJC LAX EMBRAER EMB-135KL
2004-01-01 17:00 Southwest Airlines 1664 SJC LAX BOEING 737-3H4
2004-01-01 16:35 Southwest Airlines 990 SJC BUR BOEING 737-3Q8
2004-01-01 16:10 Southwest Airlines 1383 SJC LAX BOEING 737-7H4
2004-01-01 15:32 Sky West Airlines 6980 SJC LAX BOMBARDIER INC CL-600-2B19
2004-01-01 15:30 Southwest Airlines 624 SJC LAX BOEING 737-7H4
2004-01-01 15:13 American Eagle Airlines 3168 SJC LAX EMBRAER EMB-135KL
2004-01-01 14:34 American Eagle Airlines 3160 SJC LAX EMBRAER EMB-135KL
2004-01-01 14:02 American Eagle Airlines 3148 SJC LAX EMBRAER EMB-135KL
2004-01-01 13:25 Southwest Airlines 197 SJC BUR BOEING 737-5H4
2004-01-01 13:17 Sky West Airlines 6948 SJC LAX BOMBARDIER INC CL-600-2B19
2004-01-01 13:15 Southwest Airlines 2226 SJC LAX BOEING 737-3H4
2004-01-01 11:56 American Eagle Airlines 3198 SJC LAX EMBRAER EMB-135KL
2004-01-01 10:35 Southwest Airlines 603 SJC BUR BOEING 737-3G7
2004-01-01 10:24 American Eagle Airlines 3200 SJC LAX EMBRAER EMB-135KL
2004-01-01 10:15 Southwest Airlines 1593 SJC LAX BOEING 737-3Q8
2004-01-01 09:40 Southwest Airlines 1478 SJC BUR BOEING 737-5H4
2004-01-01 09:31 American Eagle Airlines 3196 SJC LAX EMBRAER EMB-135KL
2004-01-01 09:30 Southwest Airlines 1323 SJC LAX BOEING 737-3H4
2004-01-01 08:52 Sky West Airlines 6946 SJC LAX BOMBARDIER INC CL-600-2B19
2004-01-01 08:24 American Eagle Airlines 3194 SJC LAX EMBRAER EMB-135KL
2004-01-01 06:34 Sky West Airlines 6993 SJC LAX BOMBARDIER INC CL-600-2B19
2004-01-01 06:14 American Eagle Airlines 3192 SJC LAX EMBRAER EMB-135KL

Sessionizing Flight Data.

You can think of flight data as event data. The below is a classic map/reduce roll up of the filght data by carrier and day, plane and day, and individual events for each plane.

  query: sessionize is {
    group_by: flight_date is dep_time.day
    group_by: carrier
    aggregate: daily_flight_count is flight_count
    nest: per_plane_data is {
      top: 20
      group_by: tail_num
      aggregate: plane_flight_count is flight_count
      nest: flight_legs is {
        order_by: 2
        group_by: [
          tail_num
          dep_minute is dep_time.minute
          origin_code
          dest_code is destination_code
          dep_delay
          arr_delay
        ]
      }
    }
  }
query: flights {where: [carrier:'WN', dep_time: @2002-03-03]} -> sessionize
flight_​date carrier daily_​flight_​count per_​plane_​data
[object Object] WN 2,411
tail_​num plane_​flight_​count flight_​legs
N721WN 12
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N721WN 2002-03-03 06:30 HOU AUS 0 1
N721WN 2002-03-03 07:35 AUS DAL 0 -2
N721WN 2002-03-03 08:55 DAL SAT 0 0
N721WN 2002-03-03 10:20 SAT HOU 0 0
N721WN 2002-03-03 11:30 HOU DAL 0 0
N721WN 2002-03-03 12:50 DAL SAT 0 -5
N721WN 2002-03-03 14:15 SAT HOU 5 0
N721WN 2002-03-03 15:20 HOU SAT 0 4
N721WN 2002-03-03 16:34 SAT HOU 4 -4
N721WN 2002-03-03 17:40 HOU MSY 0 -2
N721WN 2002-03-03 19:10 MSY DAL 5 10
N721WN 2002-03-03 20:55 DAL AMA 5 2
N501SW 11
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N501SW 2002-03-03 09:25 AMA DAL 0 -5
N501SW 2002-03-03 10:45 DAL AUS 0 1
N501SW 2002-03-03 11:55 AUS HRL 0 -4
N501SW 2002-03-03 13:20 HRL HOU 0 0
N501SW 2002-03-03 15:00 HOU DAL 60 52
N501SW 2002-03-03 16:25 DAL AMA 70 70
N501SW 2002-03-03 17:58 AMA DAL 78 75
N501SW 2002-03-03 19:20 DAL HOU 80 84
N501SW 2002-03-03 20:44 HOU HRL 79 85
N501SW 2002-03-03 21:59 HRL HOU 74 72
N501SW 2002-03-03 23:00 HOU DAL 60 68
N506SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N506SW 2002-03-03 09:16 TPA MSY 6 10
N506SW 2002-03-03 10:40 MSY HOU 20 21
N506SW 2002-03-03 12:15 HOU HRL 30 40
N506SW 2002-03-03 13:41 HRL AUS 46 34
N506SW 2002-03-03 14:51 AUS DAL 21 20
N506SW 2002-03-03 16:06 DAL TUL 16 8
N506SW 2002-03-03 17:21 TUL DAL 6 1
N506SW 2002-03-03 18:45 DAL LBB 15 10
N506SW 2002-03-03 20:05 LBB DAL 5 -3
N506SW 2002-03-03 21:32 DAL MSY 7 5
N712SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N712SW 2002-03-03 08:40 PBI BNA 0 2
N712SW 2002-03-03 10:17 BNA MDW 7 -2
N712SW 2002-03-03 12:05 MDW DTW 10 5
N712SW 2002-03-03 14:33 DTW MDW 13 15
N712SW 2002-03-03 15:10 MDW STL 20 15
N712SW 2002-03-03 16:44 STL HOU 24 10
N712SW 2002-03-03 19:20 HOU DAL 20 15
N712SW 2002-03-03 20:37 DAL OKC 22 22
N712SW 2002-03-03 21:40 OKC STL 20 13
N712SW 2002-03-03 23:12 STL MDW 12 6
N301SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N301SW 2002-03-03 06:30 ONT LAS 0 -2
N301SW 2002-03-03 07:58 LAS RNO 8 9
N301SW 2002-03-03 09:34 RNO SJC 4 -2
N301SW 2002-03-03 10:55 SJC ONT 5 -2
N301SW 2002-03-03 12:25 ONT LAS 0 10
N301SW 2002-03-03 13:55 LAS SAN 10 -5
N301SW 2002-03-03 15:10 SAN SJC 0 -3
N301SW 2002-03-03 17:05 SJC LAS 10 12
N301SW 2002-03-03 19:00 LAS PHX 20 20
N301SW 2002-03-03 21:35 PHX STL 20 18
N722WN 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N722WN 2002-03-03 09:00 MSY HOU 0 -2
N722WN 2002-03-03 10:53 HOU DAL 23 20
N722WN 2002-03-03 12:09 DAL MAF 24 25
N722WN 2002-03-03 13:34 MAF DAL 24 15
N722WN 2002-03-03 14:49 DAL SAT 19 26
N722WN 2002-03-03 16:22 SAT DAL 22 16
N722WN 2002-03-03 17:35 DAL AUS 15 12
N722WN 2002-03-03 18:45 AUS DAL 10 1
N722WN 2002-03-03 19:55 DAL SAT 0 0
N722WN 2002-03-03 21:10 SAT DAL 0 5
N311SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N311SW 2002-03-03 07:25 TUS LAS 0 2
N311SW 2002-03-03 08:15 LAS PHX 10 6
N311SW 2002-03-03 10:50 PHX ABQ 20 8
N311SW 2002-03-03 12:15 ABQ AMA 10 12
N311SW 2002-03-03 14:35 AMA ABQ 15 3
N311SW 2002-03-03 14:40 ABQ PHX 0 -14
N311SW 2002-03-03 16:10 PHX SJC 50 55
N311SW 2002-03-03 17:53 SJC LAX 68 53
N311SW 2002-03-03 19:26 LAX PHX 56 44
N311SW 2002-03-03 22:14 PHX ABQ 54 51
N322SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N322SW 2002-03-03 07:45 BDL BWI 0 4
N322SW 2002-03-03 09:15 BWI ALB 0 -8
N322SW 2002-03-03 10:45 ALB BWI 0 0
N322SW 2002-03-03 12:25 BWI SDF 5 10
N322SW 2002-03-03 14:45 SDF STL 15 18
N322SW 2002-03-03 15:15 STL TUL 15 10
N322SW 2002-03-03 16:59 TUL PHX 14 20
N322SW 2002-03-03 19:14 PHX BUR 29 20
N322SW 2002-03-03 19:52 BUR OAK 17 11
N322SW 2002-03-03 21:38 OAK SLC 28 22
N515SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N515SW 2002-03-03 08:05 OKC DAL 0 5
N515SW 2002-03-03 09:50 DAL LBB 0 5
N515SW 2002-03-03 11:22 LBB AUS 12 10
N515SW 2002-03-03 12:35 AUS HOU 0 4
N515SW 2002-03-03 14:02 HOU MSY 12 10
N515SW 2002-03-03 15:48 MSY MCO 33 25
N515SW 2002-03-03 18:40 MCO FLL 25 16
N515SW 2002-03-03 19:50 FLL MCO 10 15
N515SW 2002-03-03 21:10 MCO MSY 10 5
N515SW 2002-03-03 22:15 MSY HOU 5 13
N333SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N333SW 2002-03-03 08:10 FLL MCO 0 -5
N333SW 2002-03-03 09:35 MCO IND 0 -10
N333SW 2002-03-03 12:37 IND MDW 7 4
N333SW 2002-03-03 13:00 MDW BWI 20 21
N333SW 2002-03-03 16:10 BWI ORF 55 60
N333SW 2002-03-03 17:20 ORF BWI 60 48
N333SW 2002-03-03 18:30 BWI ORF 50 58
N333SW 2002-03-03 19:45 ORF MCO 60 67
N333SW 2002-03-03 22:20 MCO BNA 65 52
N333SW 2002-03-03 23:26 BNA MDW 46 43
N600WN 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N600WN 2002-03-03 08:00 ELP DAL 0 -12
N600WN 2002-03-03 11:00 DAL TUL 0 -1
N600WN 2002-03-03 12:10 TUL DAL 0 -3
N600WN 2002-03-03 13:25 DAL MAF 0 6
N600WN 2002-03-03 14:55 MAF DAL 5 -1
N600WN 2002-03-03 16:19 DAL SAT 4 0
N600WN 2002-03-03 17:40 SAT HOU 0 -14
N600WN 2002-03-03 18:55 HOU SAT 0 -3
N600WN 2002-03-03 20:10 SAT LAS 0 -2
N600WN 2002-03-03 21:38 LAS LAX 13 -4
N613SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N613SW 2002-03-03 07:25 STL PHX 0 -28
N613SW 2002-03-03 10:30 PHX LAX 0 -14
N613SW 2002-03-03 11:25 LAX SJC 0 -5
N613SW 2002-03-03 13:12 SJC LAS 12 8
N613SW 2002-03-03 14:56 LAS ONT 11 3
N613SW 2002-03-03 16:22 ONT LAS 17 13
N613SW 2002-03-03 17:57 LAS RNO 37 40
N613SW 2002-03-03 19:40 RNO LAS 40 38
N613SW 2002-03-03 21:24 LAS BUR 49 45
N613SW 2002-03-03 22:47 BUR LAS 52 52
N607SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N607SW 2002-03-03 06:50 MAF HOU 0 -7
N607SW 2002-03-03 08:20 HOU JAN 0 -8
N607SW 2002-03-03 09:50 JAN BWI 0 -22
N607SW 2002-03-03 13:25 BWI PVD 0 -7
N607SW 2002-03-03 14:51 PVD BWI 126 126
N607SW 2002-03-03 16:45 BWI ORF 135 146
N607SW 2002-03-03 17:56 ORF JAX 141 148
N607SW 2002-03-03 20:15 JAX ORF 150 120
N607SW 2002-03-03 21:47 ORF BWI 112 95
N607SW 2002-03-03 23:04 BWI ORF 114 120
N90SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N90SW 2002-03-03 10:00 LBB DAL 0 -11
N90SW 2002-03-03 11:25 DAL IAH 0 9
N90SW 2002-03-03 12:45 IAH DAL 0 0
N90SW 2002-03-03 14:00 DAL HOU 0 2
N90SW 2002-03-03 15:25 HOU CRP 5 7
N90SW 2002-03-03 16:40 CRP HOU 15 13
N90SW 2002-03-03 17:38 HOU DAL 8 3
N90SW 2002-03-03 18:45 DAL IAH 0 -5
N90SW 2002-03-03 20:05 IAH DAL 0 -10
N90SW 2002-03-03 21:30 DAL MAF 5 2
N664WN 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N664WN 2002-03-03 06:35 SEA SMF 0 -10
N664WN 2002-03-03 08:45 SMF ONT 0 -8
N664WN 2002-03-03 10:24 ONT PHX 4 -3
N664WN 2002-03-03 13:10 PHX SLC 15 14
N664WN 2002-03-03 15:09 SLC BOI 14 5
N664WN 2002-03-03 16:30 BOI PDX 10 6
N664WN 2002-03-03 17:10 PDX OAK 15 1
N664WN 2002-03-03 19:05 OAK LAX 5 -5
N664WN 2002-03-03 21:00 LAX LAS 0 -6
N664WN 2002-03-03 22:30 LAS PHX 0 -6
N365SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N365SW 2002-03-03 07:25 ALB BWI 0 0
N365SW 2002-03-03 09:05 BWI BNA 0 9
N365SW 2002-03-03 10:35 BNA LAS 5 14
N365SW 2002-03-03 13:32 LAS BUR 22 18
N365SW 2002-03-03 15:09 BUR OAK 34 33
N365SW 2002-03-03 16:56 OAK BUR 46 45
N365SW 2002-03-03 18:24 BUR SMF 49 54
N365SW 2002-03-03 20:09 SMF BUR 59 55
N365SW 2002-03-03 21:27 BUR LAS 47 41
N365SW 2002-03-03 22:36 LAS ABQ 36 34
N89SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N89SW 2002-03-03 08:00 DAL HOU 0 -3
N89SW 2002-03-03 09:35 HOU DAL 5 0
N89SW 2002-03-03 10:45 DAL OKC 0 -2
N89SW 2002-03-03 11:50 OKC DAL 0 -5
N89SW 2002-03-03 13:05 DAL IAH 0 -8
N89SW 2002-03-03 14:20 IAH DAL 0 -1
N89SW 2002-03-03 15:40 DAL ABQ 0 0
N89SW 2002-03-03 16:55 ABQ AMA 0 5
N89SW 2002-03-03 19:10 AMA DAL 0 0
N89SW 2002-03-03 20:30 DAL IAH 0 4
N93SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N93SW 2002-03-03 10:45 MAF ABQ 0 2
N93SW 2002-03-03 11:24 ABQ ELP 14 12
N93SW 2002-03-03 12:31 ELP SAT 11 14
N93SW 2002-03-03 15:19 SAT HOU 19 6
N93SW 2002-03-03 16:30 HOU AUS 30 23
N93SW 2002-03-03 17:25 AUS MAF 15 14
N93SW 2002-03-03 18:44 MAF AUS 4 0
N93SW 2002-03-03 20:00 AUS HOU 0 -11
N93SW 2002-03-03 21:14 HOU AUS 9 4
N93SW 2002-03-03 22:10 AUS ELP 0 5
N510SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N510SW 2002-03-03 07:45 LAX RNO 0 -5
N510SW 2002-03-03 09:25 RNO BOI 0 -3
N510SW 2002-03-03 11:55 BOI GEG 0 -5
N510SW 2002-03-03 12:20 GEG BOI 0 -2
N510SW 2002-03-03 14:40 BOI RNO 0 -7
N510SW 2002-03-03 15:10 RNO LAX 0 -7
N510SW 2002-03-03 17:05 LAX TUS 10 2
N510SW 2002-03-03 19:40 TUS LAX 0 -6
N510SW 2002-03-03 20:40 LAX SJC 0 1
N510SW 2002-03-03 22:10 SJC PDX 0 -4
N616SW 9
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N616SW 2002-03-03 07:34 STL MDW 4 3
N616SW 2002-03-03 08:55 MDW BWI 0 7
N616SW 2002-03-03 12:15 BWI ALB 5 2
N616SW 2002-03-03 13:50 ALB BWI 10 9
N616SW 2002-03-03 15:40 BWI CLE 25 14
N616SW 2002-03-03 17:23 CLE MDW 28 21
N616SW 2002-03-03 18:08 MDW CMH 33 28
N616SW 2002-03-03 20:43 CMH MDW 43 43
N616SW 2002-03-03 21:25 MDW BWI 55 49

The Malloy Model

All of the queries above are executed against the following model:

explore: airports is table('malloy-data.faa.airports') {
  primary_key: code
  dimension: name is concat(code, ' - ', full_name)
  measure: airport_count is count()
}

explore: carriers is table('malloy-data.faa.carriers') {
  primary_key: code
  measure: carrier_count is count()
}

explore: aircraft_models is table('malloy-data.faa.aircraft_models') {
  primary_key: aircraft_model_code
  measure: aircraft_model_count is count()
}

explore: aircraft is table('malloy-data.faa.aircraft') {
  primary_key: tail_num
  measure: aircraft_count is count()
  join: aircraft_models on aircraft_model_code
}

explore: aircraft_facts is from(
  table('malloy-data.faa.flights') -> {
    group_by: tail_num
    aggregate: [
      lifetime_flights is count()
      lifetime_distance is distance.sum()
    ]
  }
) {
  primary_key: tail_num
  dimension: lifetime_flights_bucketed is floor(lifetime_flights/1000)*1000
}

explore: flights is table('malloy-data.faa.flights') {
  primary_key: id2
  rename: origin_code is origin
  rename: destination_code is destination

  join: carriers on carrier
  join: origin is airports on origin_code
  join: destination is airports on destination_code
  join: aircraft on tail_num
  join: aircraft_facts on tail_num

  measure: [
    flight_count is count()
    total_distance is sum(distance)
    seats_for_sale is sum(aircraft.aircraft_models.seats)
    seats_owned is aircraft.sum(aircraft.aircraft_models.seats)
    -- average_seats is aircraft.aircraft_models.avg(aircraft.aircraft_models.seats)
    -- average_seats is aircraft.aircraft_models.seats.avg()
  ]

  query: measures is {
    aggregate: [
      flight_count
      aircraft.aircraft_count
      dest_count is destination.airport_count
      origin_count is origin.airport_count
    ]
  }

  -- shows carriers and number of destinations (bar chart)
  query: by_carrier is {
    group_by: carriers.nickname
    aggregate: flight_count
    aggregate: destination_count is destination.count()
  }

  -- shows year over year growth (line chart)
  query: year_over_year is {
    group_by: dep_month is month(dep_time)
    aggregate: flight_count
    group_by: dep_year is dep_time.year
  }

  -- shows plane manufacturers and frequency of use
  query: by_manufacturer is {
    top: 5
    group_by: aircraft.aircraft_models.manufacturer
    aggregate: [ aircraft.aircraft_count, flight_count ]
  }

  query: delay_by_hour_of_day is {
    where: dep_delay >30
    group_by: dep_hour is hour(dep_time)
    aggregate: flight_count
    group_by: delay is FLOOR(dep_delay)/30 * 30
  }

  query: carriers_by_month is {
    group_by: dep_month is dep_time.month
    aggregate: flight_count
    group_by: carriers.nickname
  }

  query: seats_by_distance is {
    -- seats rounded to 5
    group_by: seats is floor(aircraft.aircraft_models.seats/5)*5
    aggregate: flight_count
    -- distance rounded to 20
    group_by: distance is floor(distance/20)*20
  }

  query: routes_map is {
    group_by: [
      origin.latitude
      origin.longitude
      latitude2 is destination.latitude
      longitude2 is destination.longitude
    ]
    aggregate: flight_count
  }

  query: destinations_by_month is {
    group_by: dep_month is dep_time.month
    aggregate: flight_count
    group_by: destination.name
  }

  -- explore flights : [origin.code : 'SJC'] | airport_dashboard
  query: airport_dashboard is {
    top: 10
    group_by: code is destination_code
    group_by: destination is destination.full_name
    aggregate: flight_count
    nest: [carriers_by_month, routes_map, delay_by_hour_of_day]
  }

  query: plane_usage is {
    order_by: 1 desc
    where: aircraft.aircraft_count > 1
    group_by: aircraft_facts.lifetime_flights_bucketed
    aggregate: [aircraft.aircraft_count, flight_count]
    nest: [by_manufacturer, by_carrier]
  }


  -- query: southwest_flights is carrier_dashboard {where: carriers.nickname : 'Southwest'}
  query: carrier_dashboard is {
    aggregate: destination_count is destination.airport_count
    aggregate: flight_count
    nest: by_manufacturer
    nest: by_month is {
      group_by: dep_month is dep_time.month
      aggregate: flight_count
    }
    nest: hubs is {
      top: 10
      where: destination.airport_count > 1
      group_by: hub is origin.name
      aggregate: destination_count is destination.airport_count
    }
    nest: origin_dashboard is {
      top: 10
      group_by: [
        code is origin_code,
        origin is origin.full_name,
        origin.city
      ]
      aggregate: flight_count
      nest: [ destinations_by_month, routes_map, year_over_year]
    }
  }

  query: detail is {
    top: 30 by dep_time
    project: [
      id2, dep_time, tail_num, carrier, origin_code, destination_code, distance, aircraft.aircraft_model_code
    ]
  }

-- query that you might run for to build a flight search interface
--   explore flights : [origin.code: 'SJC', destination.code:'LAX'|'BUR', dep_time: @2004-01-01] | kayak
  query: kayak is {
    nest: carriers is {
      group_by: carriers.nickname
      aggregate: flight_count
    }
    nest: by_hour is {
      order_by: 1
      group_by: dep_hour is hour(dep_time)
      aggregate: flight_count
    }
    nest: flights is {
      group_by: [
        dep_minute is dep_time.minute
        carriers.name
        flight_num
        origin_code
        destination_code
        aircraft.aircraft_models.manufacturer
        aircraft.aircraft_models.model
      ]
    }
  }

-- example query that shows how you can build a map reduce job to sessionize flights
  query: sessionize is {
    group_by: flight_date is dep_time.day
    group_by: carrier
    aggregate: daily_flight_count is flight_count
    nest: per_plane_data is {
      top: 20
      group_by: tail_num
      aggregate: plane_flight_count is flight_count
      nest: flight_legs is {
        order_by: 2
        group_by: [
          tail_num
          dep_minute is dep_time.minute
          origin_code
          dest_code is destination_code
          dep_delay
          arr_delay
        ]
      }
    }
  }

  -- search_index is (index : [dep_time: @2004-01]
  --   *, carriers.*,
  --   origin.code, origin.state, origin.city, origin.full_name, origin.fac_type
  --   destination.code, destination.state, destination.city, destination.full_name
  --   aircraft.aircraft_model_code, aircraft.aircraft_models.manufacturer
  --   aircraft.aircraft_models.model
  --   on flight_count
  -- )
}

Data Styles

The data styles tell the Malloy renderer how to render different kinds of results.

{
  "by_carrier": {
    "renderer": "bar_chart"
  },
  "year_over_year": {
    "renderer": "line_chart"
  },
  "by_month": {
    "renderer": "line_chart"
  },
  "by_manufacturer": {
    "renderer": "bar_chart"
  },
  "routes_map": {
    "renderer": "segment_map"
  },
  "destinations_by_month": {
    "renderer": "line_chart"
  },
  "delay_by_hour_of_day": {
    "renderer" : "scatter_chart"
  },
  "seats_by_distance": {
    "renderer": "scatter_chart"
  },
  "carriers_by_month" : {
    "renderer": "line_chart"
  }
}